var mysql=require('mysql');
//建立数据库连接
var db=mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'tiantianpeiwan',
    timezone:'08:00'
});
var indexmodule={
    'getgame': function (fn) {
        var sql = `SELECT * FROM p_game `;
        db.query(sql, function (err, data) {
            fn(err, data)
        })
    },
    'getRecommend':function(fn) {
        var sql = `SELECT * FROM p_user  ORDER BY  RAND() LIMIT 2`;
        db.query(sql, function (err, data) {
            fn(err,data)
        })
    },
    'getHot':function (fn) {
        var sql=`SELECT  *, count(distinct a.U_co) FROM (p_playuser a LEFT JOIN p_game b ON a.G_id=b.G_id) LEFT JOIN   p_user c   ON c.U_co=a.U_co   GROUP BY a.U_co ORDER BY RAND() LIMIT 8`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getnewstar':function (fn) {
        var sql=`SELECT * FROM p_user ORDER BY U_date desc LIMIT 6`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getHotweek':function (fn) {
        var sql=`SELECT *,COUNT(*) AS count FROM p_jdtable a LEFT JOIN p_user b ON a.U_co=b.U_co WHERE JD_puon IN('已评价','待评价') and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(JD_datetimes) GROUP BY U_co1  ORDER BY count DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getHottotal':function (fn) {
        var sql=`SELECT *,COUNT(*) AS COUNT FROM p_jdtable a LEFT JOIN p_user b ON a.U_co=b.U_co WHERE JD_puon IN('已评价','待评价')  GROUP BY U_co1  ORDER BY COUNT DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getgiftweek':function (fn) {
        var sql=`select *,c.GA_price*a.GF_num as money,count(c.GA_price*a.GF_num) as count from p_gift a LEFT JOIN p_user b on a.U_co1=b.U_co LEFT JOIN p_giftattribute c on a.GF_gagift=c.GA_id  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(GF_data) group by U_co1 ORDER BY money DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getgifttotal':function (fn) {
        var sql=`select *,c.GA_price*a.GF_num as money,count(c.GA_price*a.GF_num) as count from p_gift a LEFT JOIN p_user b on a.U_co1=b.U_co LEFT JOIN p_giftattribute c on a.GF_gagift=c.GA_id   group by U_co1 ORDER BY money DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getRichweek':function (fn) {
        var sql=`select *,c.GA_price*a.GF_num as money,count(*) as count from p_gift a LEFT JOIN p_user b on b.U_co=a.U_co LEFT JOIN p_giftattribute c on a.GF_gagift=c.GA_id WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(GF_data) group by b.U_co ORDER BY money DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
    'getRichtotal':function (fn) {
        var sql=`select *,c.GA_price*a.GF_num as money,count(*) as count from p_gift a LEFT JOIN p_user b on b.U_co=a.U_co LEFT JOIN p_giftattribute c on a.GF_gagift=c.GA_id   group by b.U_co ORDER BY money DESC LIMIT 10`;
        db.query(sql,function (err,data) {
            fn(err,data)
        })
    },
};
module.exports=indexmodule;